package com.han.hotelplat.service.cashextract.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.han.hotelplat.comm.Constant;
import com.han.hotelplat.comm.base.EntityManage;
import com.han.hotelplat.comm.base.Pager;
import com.han.hotelplat.comm.utils.AreaHelp;
import com.han.hotelplat.comm.utils.DateUtil;
import com.han.hotelplat.comm.utils.ListSortUtil;
import com.han.hotelplat.comm.utils.StrUtil;
import com.han.hotelplat.dao.cashextract.CashextractDao;
import com.han.hotelplat.pojo.cashextract.CashExtract;
import com.han.hotelplat.pojo.systemuser.Systemuser;
import com.han.hotelplat.service.bill.BillService;
import com.han.hotelplat.service.cashextract.CashExtractService;
import com.han.hotelplat.service.systemuser.SystemuserService;

@Service
public class CashExtractServiceImpl implements CashExtractService{
	@Autowired
	private CashextractDao cashextractDao;
	@Autowired
	private  EntityManage entityManage;
	@Autowired
	private SystemuserService systemuserService;
	
	@SuppressWarnings("rawtypes")
	@Override
	public float getMaxCash(String userid) {
		//查询本月已提现的金额
		String sql = "select sum(money) from tb_cash_extract where state in (1,2,4) "
				+ " and DATE_FORMAT( edittime, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' )"
				+ " and userid='"+userid+"'";
		List re = entityManage.findBySql(sql);
		float allcount = 0;
		if(re != null){
			if(re.get(0)!=null){
				allcount = Float.parseFloat(re.size() == 0?"0":re.get(0).toString());//本月
			}
		}
		return Constant.MAX_EX_CASH-allcount;
	}
	
	@Override
	public List<CashExtract> getTodayExCash(String userid,Integer state) {
		String hql = "from CashExtract where userid = '"+userid+"'"
				+ " and creattime>='"+DateUtil.formatYMD(new Date())+" 00:00:00'";
		
		if(state != null){
			hql=hql+" and state="+state;
		}
		List<CashExtract> li  = cashextractDao.findByHQL(hql);
		return li;
	}
	
	@Override
	public void save(CashExtract cashExtract) {
		cashextractDao.save(cashExtract);
	}
	@Override
	public Pager getAllCashExtByPage(String startDate, String endDate, Integer state, Integer sortType, 
			Integer areaCode, String condition, Integer pageNo, String hotelName) {
		Pager<CashExtract> data = new Pager<CashExtract>(20, 0, 0, 0, new ArrayList<CashExtract>());
		StringBuffer hql = new StringBuffer("select new CashExtract(ce.id,ce.userid,em.tel,em.realname,em.area,"
				+ "ce.money,ce.creattime, ce.state,em.bankcard,ce.serial,ce.querytimes, ce.reqsn, "
				+ " ce.edittime, ce.authuserid) "
				+ "from CashExtract as ce, Employee as em "
				+ " where ce.userid=em.userid ");
		if(!StrUtil.isNullOrEmpty(startDate)){
			hql.append(" and ce.creattime>= '"+startDate+" 00:00:00'");
		}
		
		if(!StrUtil.isNullOrEmpty(endDate)){
			hql.append(" and ce.creattime<='"+startDate+" 23:59:59'");
		}
		
		if(state != null){
			hql.append(" and ce.state="+state);
		}
		
		if(areaCode != null && areaCode.intValue()>100000){//区域码至少6位
			String w_areaCode = areaCode.toString();
			int x=0;
			String w_area="";
			if(w_areaCode.endsWith("0000")) {
				//精度 省份
				x=9999;
			}else if(w_areaCode.endsWith("00")) {
				//精度 市
				x=99;
			}else {
				//精度 区
				x=0;
			}
			//要的   - 条件  >=0
			//410610-410000 <= 9999
			//410610-410600 <= 99
			//410610-410610 == 0
			w_area=" and convert(em.area,signed) - "+areaCode +" >=0 ";
			w_area+=" and convert(em.area,signed) - "+areaCode +" <= "+x;
			hql.append(w_area);
		}
		
		if(!StrUtil.isNullOrEmpty(condition)){
			hql.append(" and (em.realname like '%"+condition+"%' "
					+ " or em.tel like '%"+condition+"%'"
					+ " or ce.serial like '%"+condition+"%'"
					+ ")");
		}
		
		if(StringUtils.isNotBlank(hotelName)){
			String sql = "SELECT et.emid FROM tb_hotel AS h, tb_em_task AS et "
					+ "WHERE et.hotelid=h.id AND h.hotelname like '%"+hotelName+"%' GROUP BY et.emid";
			List<String> li = entityManage.findBySql(sql);
			StringBuffer sb = new StringBuffer(" and em.userid in(");
			if(li.size() == 0){
				return data;
			}
			for(int i=0; i<li.size(); i++){
				String emid = li.get(i);
				if(i == (li.size()-1)){
					sb.append("'"+emid +"')");
				}else{
					sb.append("'"+emid+"',");
				}
			}
			
			hql.append(sb.toString());
		}
		if(sortType != null && sortType.intValue() == 1){//升序
			hql.append(" order by ce.edittime asc");
		}else if(sortType != null && sortType.intValue() == 2){//升序
			hql.append( " order by ce.edittime desc");
		}else{
			hql.append(" order by ce.creattime desc");
		}
		data = entityManage.findByHqlForPage(hql.toString(), pageNo, (short)20);
		for (CashExtract ce : data.getList()) {
			String sql = "select balance from tb_wallet where userid = '"+ce.getUserid()+"'";
			List li = entityManage.findBySql(sql);
			float balance = Float.parseFloat(li.size() >0 ? li.get(0).toString():"0");
			ce.setBalance(balance);
			if(ce.getArea() != null){
				ce.setAreaModel(AreaHelp.getInstance().getAreaModels(ce.getArea()));
			}
			if(ce.getAuthuserid() != null){
				Systemuser systemUser = systemuserService.findUserByUserid(ce.getAuthuserid());
				if(systemUser != null){
					ce.setAuthusername(systemUser.getRealname());
					ce.setAuthtime(ce.getEdittime());
				}else{
					ce.setAuthusername("");
					ce.setAuthtime(ce.getEdittime());
				}
			}
		}
		
		//ListSortUtil<CashExtract> sort = new ListSortUtil<CashExtract>();
		/*if(sortType != null && sortType.intValue() == 1){
			sort.sort(data.getList(), "creattime", true);
		}else{
			sort.sort(data.getList(), "creattime", false);
		}*/
		return data;
	}
	@Override
	public CashExtract findCashExById(Integer id) {
		return cashextractDao.findById(id);
	}
	@Override
	public void update(CashExtract data) {
		cashextractDao.update(data);		
	}
	@Override
	public List<CashExtract> findCashEx(String userid, Integer state,
			String starttime, String endtime, String serial) {
		StringBuffer hql = new StringBuffer("select new CashExtract(ce.id,ce.userid,em.tel,em.realname,em.area,"
				+ "ce.money,ce.creattime, ce.state,em.bankcard,ce.serial, ce.querytimes,ce.reqsn) "
				+ "from CashExtract as ce, Employee as em "
				+ " where ce.userid=em.userid ");
		if(!StrUtil.isNullOrEmpty(userid)){
			hql.append(" and ce.userid='"+userid+"'");
		}
		if(state != null && state.intValue()>0){
			hql.append(" and ce.state="+state);
		}
		
		if(!StrUtil.isNullOrEmpty(starttime)){
			hql.append(" and ce.creattime>= '"+starttime+" 00:00:00'");
		}
		
		if(!StrUtil.isNullOrEmpty(endtime)){
			hql.append(" and ce.creattime<='"+endtime+" 23:59:59'");
		}
		
		if(!StrUtil.isNullOrEmpty(serial)){
			hql.append(" and ce.serial='"+serial+"'");
		}
		List<CashExtract> data = entityManage.findByHql(hql.toString());
		return data;
	}

	@Override
	public CashExtract getCashExtById(Integer id) {
		StringBuffer hql = new StringBuffer("select new CashExtract(ce.id,ce.userid,em.tel,em.realname,em.area,"
				+ "ce.money,ce.creattime, ce.state,em.bankcard,ce.serial,ce.querytimes, ce.reqsn, ce.remark) "
				+ "from CashExtract as ce, Employee as em "
				+ " where ce.userid=em.userid and ce.id="+id);
		List<CashExtract> data = entityManage.findByHql(hql.toString());
		if(data.size() != 0){
			return data.get(0);
		}
		return null;
	}

	@Override
	public List<CashExtract> exportCashExList(String startDate,
			String endDate, Integer state, Integer sortType, Integer areaCode,
			String condition) {
		StringBuffer hql = new StringBuffer("select new CashExtract(ce.id,ce.userid,em.tel,em.realname,em.area,"
				+ "ce.money,ce.creattime, ce.state,em.bankcard,ce.serial,ce.querytimes, ce.reqsn) "
				+ "from CashExtract as ce, Employee as em "
				+ " where ce.userid=em.userid ");
		if(!StrUtil.isNullOrEmpty(startDate)){
			hql.append(" and ce.creattime>= '"+startDate+" 00:00:00'");
		}
		
		if(!StrUtil.isNullOrEmpty(endDate)){
			hql.append(" and ce.creattime<='"+startDate+" 23:59:59'");
		}
		
		if(state != null){
			hql.append(" and ce.state="+state);
		}
		
		if(areaCode != null && areaCode.intValue()>100000){//区域码至少6位
			String w_areaCode = areaCode.toString();
			int x=0;
			String w_area="";
			if(w_areaCode.endsWith("0000")) {
				//精度 省份
				x=9999;
			}else if(w_areaCode.endsWith("00")) {
				//精度 市
				x=99;
			}else {
				//精度 区
				x=0;
			}
			//要的   - 条件  >=0
			//410610-410000 <= 9999
			//410610-410600 <= 99
			//410610-410610 == 0
			w_area=" and convert(em.area,signed) - "+areaCode +" >=0 ";
			w_area+=" and convert(em.area,signed) - "+areaCode +" <= "+x;
			hql.append(w_area);
		}
		
		if(!StrUtil.isNullOrEmpty(condition)){
			hql.append(" and (em.realname like '%"+condition+"%' "
					+ " or em.tel like '%"+condition+"%'"
					+ " or ce.serial like '%"+condition+"%'"
					+ ")");
		}

		if(sortType != null && sortType.intValue() == 1){//升序
			hql.append(hql.length() + " order by ce.creattime asc");
		}else if(sortType != null && sortType.intValue() == 2){//升序
			hql.append(" order by ce.creattime desc");
		}else{
			hql.append(" order by ce.creattime desc");
		}
		
		List<CashExtract> data = entityManage.findByHql(hql.toString());
		for (CashExtract ce : data) {
			if(ce.getArea() != null){
				ce.setAreaModel(AreaHelp.getInstance().getAreaModels(ce.getArea()));
			}
		}
		return data;
	}

}
